Release 10.1A: OpenEdge Data Management:
SQL Development

Preface

This Preface contains the following sections:

Purpose
Audience
Organization
Typographical conventions
Examples of syntax diagrams (SQL)

Purpose

OpenEdge Data Management: SQL Development provides information for developers who are using SQL within the OpenEdge® application development environment. The information in this manual is also useful for database administrators, and, to a lesser degree, to application end users.

Audience

The audience for this book is composed of three specific groups:

Database Administrators who will use the book to:
Create and maintain databases.
Create, modify, and revoke user privileges.
Tune database performance.
Perform installation and setup of servers and clients.
Application developers who will use the book to:
Manage database connections and set up data sources.
Create queries to draw information from the database.
Tune database queries.
Develop application business logic.
End users who will use the book to:
Connect to databases.
Call stored procedures.
Issue queries.
Understand error messages.

Organization

Chapter 1 "Introduction"

Provides an overview of OpenEdge SQL and the OpenEdge SQL client/server architecture.

Chapter 2 "JDBC Client"

Presents an overview of the JDBC client, details on setting environment variables, and procedures for connecting to a database with the JDBC driver.

Chapter 3 "ODBC Client"

Offers an overview of the ODBC client and information on configuring data source.

Chapter 4 "Data Control Language and Security"

Reviews information on working with database security, creating users, and granting, modifying, and revoking privileges.

Chapter 5 "OpenEdge SQL Data Definition Language"

Furnishes information on OpenEdge SQL database structure and methods for creating, altering, and dropping database objects.

Chapter 6 "OpenEdge SQL Data Manipulation Language"

Provides information on the Data Manipulation Language statements, indexes, and join operations.

Chapter 7 "Progress 4GL and OpenEdge SQL Interoperability"

Addresses the interoperability of Progress 4GL and OpenEdge SQL.

Chapter 8 "Data Control Language and Transaction Behavior"

Summarizes information on transactions, isolation levels, and locking.

Chapter 9 "Stored Procedures and Triggers"

Provides information on using stored procedures and triggers.

Chapter 10 "Optimizing Query Performance"

Presents information on the operation of the SQL Query Optimizer and offers recommendations on how to get the best out of its performance.

Typographical conventions

This manual uses the following typographical conventions:

Convention	Description
Bold	Bold typeface indicates commands or characters the user types, provides emphasis, or the names of user interface elements.
Italic	Italic typeface indicates the title of a document, or signifies new terms.
SMALL, BOLD CAPITAL LETTERS	Small, bold capital letters indicate OpenEdge® key functions and generic keyboard keys; for example, GET and CTRL.
KEY1+KEY2	A plus sign between key names indicates a simultaneous key sequence: you press and hold down the first key while pressing the second key. For example, CTRL+X.
KEY1 KEY2	A space between key names indicates a sequential key sequence: you press and release the first key, then press another key. For example, ESCAPE H.
Syntax:
Fixed width	A fixed-width font is used in syntax statements, code examples, system output, and filenames.
Fixed-width italics	Fixed-width italics indicate variables in syntax statements.
Fixed-width bold	Fixed-width bold indicates variables with special emphasis.
UPPERCASE fixed width	Uppercase words are Progress® 4GL language keywords. Although these are always shown in uppercase, you can type them in either uppercase or lowercase in a procedure.
	This icon (three arrows) introduces a multi-step procedure.
	This icon (one arrow) introduces a single-step procedure.
lowercase	Lowercase words are source language elements that are not SQL keywords.
[ ]	Large brackets indicate the items within them are optional.
{ }	Large braces indicate the items within them are required. They are used to simplify complex syntax diagrams.
|	A vertical bar indicates a choice.
...	Ellipses indicate repetition: you can choose one or more of the preceding items.

Examples of syntax diagrams (SQL)

In this example, GRANT, RESOURCE, DBA, and TO are keywords. You must specify RESOURCE, DBA, or both, and at least one user_name. Optionally you can specify additional user_name items; each subsequent user_name must be preceded by a comma:

Syntax

GRANT { RESOURCE, DBA } TO user_name [, user_name ] ... ;

This excerpt from an ODBC application invokes a stored procedure using the ODBC syntax { call procedure_name ( param ) }, where braces and parentheses are part of the language:

Syntax

proc1( param, "{ call proc2 (param) }", param);

In this example, you must specify a table_name, view_name, or synonym, but you can choose only one. In all SQL syntax, if you specify the optional owner_name qualifier, there must not be a space between the period separator and table_name, view_name, or synonym:

Syntax

CREATE [ PUBLIC ] SYNONYM synonym   FOR [ owner_name.]{table_name |view_name |synonym } ;

In this example, you must specify table_name or view_name:

Syntax

DELETE FROM [ owner_name.]{table_name |view_name }   [ WHERE search_condition ] ;

In this example, you must include one expression (expr) or column position (posn), and optionally you can specify the sort order as ascending (ASC) or descending (DESC). You can specify additional expressions or column positions for sorting within a sorted result set. The SQL engine orders the rows on the basis of the first expr or posn. If the values are the same, the second expr or posn is used in the ordering:

Syntax

ORDER BY { expr | posn } [ ASC | DESC ]   [ , [ { expr | posn } [ ASC | DESC ] ] ... ]

Long syntax descriptions split across lines

Some syntax descriptions are too long to fit on one line. When syntax descriptions are split across multiple lines, groups of optional and groups of required items are kept together in the required order.

In this example, CREATE VIEW is followed by several optional items:

Syntax

CREATE VIEW [ owner_name.]view_name   [ ( column_name [, column_name ] ... ) ]   AS [ ( ] query_expression [ ) ] [ WITH CHECK OPTION ] ;